﻿-- ================================================
-- Remarks: Not part of standard Stored Proc
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ivy_ContentManagement_ContentSummary_GetAllByTypeAndOrganization')
	BEGIN
		DROP  Procedure  [Ivy_ContentManagement_ContentSummary_GetAllByTypeAndOrganization]
	END

GO

CREATE PROCEDURE [Ivy_ContentManagement_ContentSummary_GetAllByTypeAndOrganization] 
-- =============================================
-- Author:		<Asha Kiran>
-- Create date: <01/12/2016>
-- Description:	<Ivy_ContentManagement_ContentSummary_GetAllByTypeAndOrganization>
-- =============================================
-- Add the parameters for the stored procedure here
	(
				@ContentTypeKey int = NULL,
				@Sys_OrganizationID int = NULL,
				@Sys_IsDeleted bit = false
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;
	
	SELECT      
				[ContentDataMaster].[ContentKey], 
				[ContentDataMaster].[ContentCode], 
				[ContentDataMaster].[ContentName], 
				[ContentDataMaster].[ContentTypeKey], 
				[ContentTypeMaster].[ContentTypeCode], 
				[ContentTypeMaster].[ContentTypeName], 
				[ContentDataMaster].[ContentStatus], 
				[ContentDataMaster].[ContentPublicationStartDate], 
				[ContentDataMaster].[ContentHtmlData], 
				[ContentDataMaster].[ContentMetaTagKeyword], 
				[ContentDataMaster].[ContentMetaTagDescription], 
				[ContentDataMaster].[ContentTotalLikes], 
				[ContentDataMaster].[ContentTotalViews], 
				[ContentDataMaster].[Sys_DateOfCreation], 
				[ContentDataMaster].[Sys_CreatedByID], 

				[Person].[PersonFirstName], 
				[Person].[PersonLastName], 

				[PersonProfileImage].[PersonProfileImageData], 
				[PersonProfileImage].[PersonImageType], 
				[PersonProfileImage].[PersonImageFileName], 

				[ContentImageData].[ContentImageKey], 
				[ContentImageData].[ContentImageData], 
				[ContentImageData].[ContentImageType], 
				[ContentImageData].[ContentImageFileName], 

				AVG([ContentRating].[ContentRatingValue]) AS [AverageRating], 
				COUNT([ContentRating].[ContentRatingValue]) AS [TotalReviews], 

				[ContentDataMaster].[Sys_DeleteType], 
				[ContentDataMaster].[Sys_IsDeleted], 
				[ContentDataMaster].[Sys_OrganizationID]
	FROM       
				[ContentImageData] RIGHT OUTER JOIN
				[PersonProfileImage] RIGHT OUTER JOIN
				[ContentDataMaster] LEFT OUTER JOIN [ContentRating] 
	ON			[ContentDataMaster].[ContentKey] = [ContentRating].[ContentKey] LEFT OUTER JOIN [Person] 
	ON			[ContentDataMaster].[Sys_CreatedByID] = [Person].[PersonKey] 
	ON			[PersonProfileImage].[PersonKey] = [Person].[PersonKey] 
	ON			[ContentImageData].[ContentKey] = [ContentDataMaster].[ContentKey] LEFT OUTER JOIN [ContentTypeMaster] 
	ON			[ContentDataMaster].[ContentTypeKey] = [ContentTypeMaster].[ContentTypeKey]
	
	GROUP BY	
				[ContentDataMaster].[ContentKey], 
				[ContentDataMaster].[ContentCode], 
				[ContentDataMaster].[ContentName], 
				[ContentDataMaster].[ContentTypeKey], 
				[ContentTypeMaster].[ContentTypeCode], 
				[ContentTypeMaster].[ContentTypeName], 
				[ContentDataMaster].[ContentStatus], 
				[ContentDataMaster].[ContentPublicationStartDate], 
				[ContentDataMaster].[ContentHtmlData], 
				[ContentDataMaster].[ContentMetaTagKeyword], 
                [ContentDataMaster].[ContentMetaTagDescription], 
				[ContentDataMaster].[ContentTotalLikes], 
				[ContentDataMaster].[ContentTotalViews], 
				[ContentDataMaster].[Sys_DateOfCreation], 
				[ContentDataMaster].[Sys_CreatedByID], 

                [Person].[PersonFirstName], 
				[Person].[PersonLastName], 

				[PersonProfileImage].[PersonProfileImageData], 
				[PersonProfileImage].[PersonImageType], 
				[PersonProfileImage].[PersonImageFileName], 

                [ContentImageData].[ContentImageKey], 
				[ContentImageData].[ContentImageData], 
				[ContentImageData].[ContentImageType], 
				[ContentImageData].[ContentImageFileName], 

				[ContentDataMaster].[Sys_DeleteType], 
                [ContentDataMaster].[Sys_OrganizationID], 
				[ContentDataMaster].[Sys_IsDeleted]
	HAVING      
				([ContentDataMaster].[Sys_IsDeleted] = ISNULL(@Sys_IsDeleted, [ContentDataMaster].[Sys_IsDeleted])) AND 
				([ContentDataMaster].[Sys_OrganizationID] = ISNULL(@Sys_OrganizationID, [ContentDataMaster].[Sys_OrganizationID])) AND 
				([ContentDataMaster].[ContentTypeKey] = ISNULL(@ContentTypeKey, [ContentDataMaster].[ContentTypeKey]))

END
GO
